﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace MINERP.DAL.Statistics
{
    public class NMShopPruductPriceDAL : DALBase
    {
        public static DataSet GetData(int inShopid, DateTime inStartTime, DateTime inEndTime)
        {
            try
            {


                Database db = null;
                db = DatabaseFactory.CreateDatabase();
                DataSet set = new DataSet();
                string strSQL = "";
                //            strSQL = "select convert(datetime,CreateDay) as CreateDay,shopid,OrderProductId,ProductName,ProductCategoryName,OrderProductCode,CtmName, Width,Height,productNum,PrintCount,ProductCount,Areas,(PrintCount+ProductCount) as AllCount " +
                //"from " +

                //" (select CONVERT(varchar(10) ,CompleteTime, 23) CreateDay,shopid,OrderProductId,ProductName,ProductCategoryName,OrderProductCode,CtmName,product.Width Width,product.Height Height,productNum from " +
                //" (select OrderProductId,ProductId,OrderProductCode,CtmName,Width,Height,productNum,CompleteTime,shopid from orderinfo_bk,Tb_OrderBag_bk where  orderinfo_bk.OrderBagId=Tb_OrderBag_bk.OrderBagId " +
                //" UNION select OrderProductId,ProductId,OrderProductCode,CtmName,Width,Height,productNum,CompleteTime,shopid from orderinfo,Tb_OrderBag where orderinfo.OrderBagId=Tb_OrderBag.OrderBagId " +
                //" ) as product ,dbo.Tb_Product,Tb_PorductCategory " +
                //"  where product.ProductId=Tb_Product.ProuctId and Tb_Product.ProductCategoryId=Tb_PorductCategory.ProductCategoryId) as ta, " +

                //" (select PrintCount,ProductCount,Areas,OrderInfoCode from  " +
                //"  (select sum(PrintCount) as PrintCount,sum(ProductCount) as ProductCount ,sum(Area) as Areas,tz.BillDetailId  as BillDetailId from " +
                //"  (select isnull(case Type when 1 then count end ,0 ) as PrintCount, " +
                //" isnull(case Type when 0 then count end ,0) as ProductCount," +
                //" isnull(case Type when 2 then count end ,0) as DesignCount,Area,BillDetailId  " +
                //" from dbo.Bill_Detail_Detail " +
                //" ) as tz group by tz.BillDetailId ) as ty,dbo.Bill_Detail where Bill_Detail.BillDetailId=ty.BillDetailId) as tb " +
                //" where ta.OrderProductCode= tb.OrderInfoCode and ta.CreateDay between Convert(datetime,'"+inStartTime+"') and Convert(datetime,'"+inEndTime+"') and ta.shopid="+inShopid;
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT     CONVERT(datetime, ta.CreateDay) AS CreateDay, ta.shopid, ta.OrderProductId, ta.ProductName, ta.ProductCategoryName, ta.OrderProductCode,                                                  ");
                stringBuilder.Append("                      ta.CtmName, ta.Width, ta.Height, ta.productNum, tb.PrintCount, tb.ProductCount, tb.Areas, tb.PrintCount + tb.ProductCount AS AllCount                                          ");
                stringBuilder.Append("FROM         (SELECT     CONVERT(varchar(10), product.CompleteTime, 23) AS CreateDay, product.shopID, product.OrderProductId, Tb_Product.ProductName,                                                ");
                stringBuilder.Append("                                              Tb_PorductCategory.ProductCategoryName, product.OrderProductCode, product.CtmName, product.Width, product.Height,                                      ");
                stringBuilder.Append("                                              product.productNum                                                                                                                                     ");
                stringBuilder.Append("                       FROM          (SELECT     orderInfo_BK.OrderProductId, orderInfo_BK.ProductId, orderInfo_BK.OrderProductCode, orderInfo_BK.CtmName,                                           ");
                stringBuilder.Append("                                                                      orderInfo_BK.Width, orderInfo_BK.Height, orderInfo_BK.productNum, Tb_OrderBag_BK.CompleteTime,                                 ");
                stringBuilder.Append("                                                                      Tb_OrderBag_BK.shopID                                                                                                          ");
                stringBuilder.Append("                                               FROM          orderInfo_BK INNER JOIN                                                                                                                 ");
                stringBuilder.Append("                                                                      Tb_OrderBag_BK ON orderInfo_BK.OrderBagId = Tb_OrderBag_BK.OrderBagId                                                          ");
                stringBuilder.Append("                                               UNION                                                                                                                                                 ");
                stringBuilder.Append("                                               SELECT     orderInfo.OrderProductId, orderInfo.ProductId, orderInfo.OrderProductCode, orderInfo.CtmName, orderInfo.Width, orderInfo.Height,           ");
                stringBuilder.Append("                                                                     orderInfo.productNum, Tb_OrderBag.CompleteTime, Tb_OrderBag.shopID                                                              ");
                stringBuilder.Append("                                               FROM         orderInfo INNER JOIN                                                                                                                     ");
                stringBuilder.Append("                                                                     Tb_OrderBag ON orderInfo.OrderBagId = Tb_OrderBag.OrderBagId) AS product INNER JOIN                                             ");
                stringBuilder.Append("                                              Tb_Product ON product.ProductId = Tb_Product.ProuctId INNER JOIN                                                                                       ");
                stringBuilder.Append("                                              Tb_PorductCategory ON Tb_Product.ProductCategoryId = Tb_PorductCategory.ProductCategoryId) AS ta INNER JOIN                                            ");
                stringBuilder.Append("                          (SELECT     ty.PrintCount, ty.ProductCount, ty.Areas, Bill_Detail.OrderInfoCode                                                                                            ");
                stringBuilder.Append("                            FROM          (SELECT     SUM(PrintCount) AS PrintCount, SUM(ProductCount) AS ProductCount, SUM(Area) AS Areas, BillDetailId                                             ");
                stringBuilder.Append("                                                    FROM          (SELECT     ISNULL(CASE Type WHEN 1 THEN count END, 0) AS PrintCount, ISNULL(CASE Type WHEN 0 THEN count END, 0)                   ");
                stringBuilder.Append("                                                                                                   AS ProductCount, ISNULL(CASE Type WHEN 2 THEN count END, 0) AS DesignCount, ISNULL(CASE Type WHEN 1 THEN Area END, 0) as Area, BillDetailId    ");
                stringBuilder.Append("                                                                            FROM          Bill_Detail_Detail) AS tz                                                                                  ");
                stringBuilder.Append("                                                    GROUP BY BillDetailId) AS ty INNER JOIN                                                                                                          ");
                stringBuilder.Append("                                                   Bill_Detail ON ty.BillDetailId = Bill_Detail.BillDetailId) AS tb ON ta.OrderProductCode = tb.OrderInfoCode                                        ");
               
                stringBuilder.Append("where ta.OrderProductCode= tb.OrderInfoCode and ta.CreateDay between Convert(datetime,'"+inStartTime+"') and Convert(datetime,'"+inEndTime+"') and ta.shopid="+inShopid);

                DbCommand dbCommand = db.GetSqlStringCommand(stringBuilder.ToString());
                set = db.ExecuteDataSet(dbCommand);
                return set;
            }
            catch (Exception exception)
            {

                throw exception;
            }
        }
        public static DataSet GetDataForFuji(int inShopid, DateTime inStartTime, DateTime inEndTime)
        {
            try
            {


                Database db = null;
                db = DatabaseFactory.CreateDatabase();
                DataSet set = new DataSet();
                string strSQL = "";
                //            strSQL = "select convert(datetime,CreateDay) as CreateDay,shopid,OrderProductId,ProductName,ProductCategoryName,OrderProductCode,CtmName, Width,Height,productNum,PrintCount,ProductCount,Areas,(PrintCount+ProductCount) as AllCount " +
                //"from " +

                //" (select CONVERT(varchar(10) ,CompleteTime, 23) CreateDay,shopid,OrderProductId,ProductName,ProductCategoryName,OrderProductCode,CtmName,product.Width Width,product.Height Height,productNum from " +
                //" (select OrderProductId,ProductId,OrderProductCode,CtmName,Width,Height,productNum,CompleteTime,shopid from orderinfo_bk,Tb_OrderBag_bk where  orderinfo_bk.OrderBagId=Tb_OrderBag_bk.OrderBagId " +
                //" UNION select OrderProductId,ProductId,OrderProductCode,CtmName,Width,Height,productNum,CompleteTime,shopid from orderinfo,Tb_OrderBag where orderinfo.OrderBagId=Tb_OrderBag.OrderBagId " +
                //" ) as product ,dbo.Tb_Product,Tb_PorductCategory " +
                //"  where product.ProductId=Tb_Product.ProuctId and Tb_Product.ProductCategoryId=Tb_PorductCategory.ProductCategoryId) as ta, " +

                //" (select PrintCount,ProductCount,Areas,OrderInfoCode from  " +
                //"  (select sum(PrintCount) as PrintCount,sum(ProductCount) as ProductCount ,sum(Area) as Areas,tz.BillDetailId  as BillDetailId from " +
                //"  (select isnull(case Type when 1 then count end ,0 ) as PrintCount, " +
                //" isnull(case Type when 0 then count end ,0) as ProductCount," +
                //" isnull(case Type when 2 then count end ,0) as DesignCount,Area,BillDetailId  " +
                //" from dbo.Bill_Detail_Detail " +
                //" ) as tz group by tz.BillDetailId ) as ty,dbo.Bill_Detail where Bill_Detail.BillDetailId=ty.BillDetailId) as tb " +
                //" where ta.OrderProductCode= tb.OrderInfoCode and ta.CreateDay between Convert(datetime,'"+inStartTime+"') and Convert(datetime,'"+inEndTime+"') and ta.shopid="+inShopid;
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT     Bill_Detail_Detail.DetailName + Bill_Detail_Detail.Spec AS ProductSpec, Bill_Detail_Detail.Num, Bill_Detail_Detail.UnitPrice, Bill_Detail_Detail.Count, ");
                stringBuilder.Append("                      SHOP_Bill.CreateTime, Shop.shopName                                                                                                          ");
                stringBuilder.Append("FROM         Bill_Detail_Detail INNER JOIN                                                                                                                         ");
                stringBuilder.Append("                      Bill_Detail ON Bill_Detail_Detail.BillDetailId = Bill_Detail.BillDetailId INNER JOIN                                                         ");
                stringBuilder.Append("                      SHOP_Bill ON Bill_Detail.BillId = SHOP_Bill.BillId INNER JOIN                                                                                ");
                stringBuilder.Append("                      Shop ON SHOP_Bill.ShopId = Shop.ShopId                                                                                                       ");
                stringBuilder.Append("WHERE     (Bill_Detail_Detail.Num > 0)                                                                                                                              ");

                stringBuilder.Append("and shop_bill.CreateTime between Convert(datetime,'" + inStartTime + "') and Convert(datetime,'" + inEndTime + "') and shop.shopid=" + inShopid);

                DbCommand dbCommand = db.GetSqlStringCommand(stringBuilder.ToString());
                set = db.ExecuteDataSet(dbCommand);
                return set;
            }
            catch (Exception exception)
            {

                throw exception;
            }
        }
        public static DataSet GetPruductInfo(DateTime inStartTime, DateTime inEndTime)
        {
            try
            {
                Database db = null;
                db = DatabaseFactory.CreateDatabase();
                DataSet set = new DataSet();
                string strSQL = "select * from Tb_FujOrderInfo where OrderDateTime between Convert(datetime,'" + inStartTime + "') and Convert(datetime,'" + inEndTime + "')";
                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
                set = db.ExecuteDataSet(dbCommand);
                return set;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }
    }
}
